﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Text;
/// <summary>
/// Summary description for ThanhVienCtr
/// </summary>
public class ThanhVienCtr
{
	public ThanhVienCtr()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public bool DangNhap(string userName,string password)
    {
        try
        {
           
            string sqlStr = "SELECT MatKhau FROM ThanhVien WHERE TenDangNhap = @TenDangNhap AND TrangThai='True' ";
            object kq = DataProvider.ExecuteScalar(sqlStr, "@TenDangNhap", userName);
            if (kq == null)
                return false;
            if (String.IsNullOrEmpty(kq.ToString()))
            {
                return false;
            }
            else
            {
                if (password.Equals(kq))
                    return true;
            }
            return false;
        }
        catch(Exception ex)
        {
            throw ex;
        }
    }
    
    public bool DangKy(EThanhVien thanhVien)
    {
        try
        {
            thanhVien.TrangThai = false;
            
            DataProvider.ExecuteNonQuery("INSERT INTO ThanhVien VAlUES"+
                "(@TenDangNhap,@MatKhau,@TenKH,@Email,@NgaySinh,@CMND,@DiaChi,@DienThoai,@ActiveCode,@TinhTrang)",
                "@TenDangNhap",thanhVien.TenDangNhap,"@MatKhau",thanhVien.MatKhau,
                "@TenKH",thanhVien.TenKH,"@Email",thanhVien.Email,"@NgaySinh",thanhVien.NgaySinh,"@CMND",thanhVien.CMND,
                "@DiaChi",thanhVien.DiaChi,"@DienThoai",thanhVien.DienThoai,"@ActiveCode",thanhVien.ActiveCode,
                "@TinhTrang", thanhVien.TrangThai);
            return true;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public EThanhVien GetThanhVien(string TenDangNhap)
    {
        try
        {
            DataTable tableThanhVien = DataProvider.GetData("Select * from ThanhVien where TenDangNhap=@TenDangNhap", "@TenDangNhap", TenDangNhap);
            EThanhVien thanhVien = new EThanhVien();
            thanhVien.TenDangNhap = tableThanhVien.Rows[0]["TenDangNhap"].ToString();
            thanhVien.MatKhau = tableThanhVien.Rows[0]["MatKhau"].ToString();
            thanhVien.TenKH = tableThanhVien.Rows[0]["TenKH"].ToString();
            thanhVien.DiaChi = tableThanhVien.Rows[0]["DiaChi"].ToString();
            thanhVien.DienThoai = tableThanhVien.Rows[0]["DienThoai"].ToString();
            thanhVien.Email = tableThanhVien.Rows[0]["Email"].ToString();
            thanhVien.NgaySinh = DateTime.Parse(tableThanhVien.Rows[0]["NgaySinh"].ToString());
            return thanhVien;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public DataTable GetThanhVien()
    {
        try
        {
            return DataProvider.GetData("SELECT *  FROM ThanhVien Where trangthai='true'");
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
     
    public DataTable GetThanhVien1()
    {
        try
        {
            return DataProvider.GetData("SELECT *  FROM ThanhVien where TrangThai='True' ");
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public bool DeleteThanhVien(string TenDangNhap)
    {
        try
        {
            DataProvider.ExecuteNonQuery("DELETE FROM ThanhVien WHERE TenDangNhap=@TenDangNhap", "@TenDangNhap", TenDangNhap);
            return true;
        }
        catch (Exception ex)
        {
            throw ex;

        }
    }
    public bool DeleteThanhVien1(string TenDangNhap)
    {
        try
        {
            DataProvider.ExecuteNonQuery("Update ThanhVien set trangthai=0 WHERE TenDangNhap=@TenDangNhap", "@TenDangNhap", TenDangNhap);
            return true;
        }
        catch (Exception ex)
        {
            throw ex;

        }
    }
    public bool Update(EThanhVien thanhVien)
    {
        try
        {

            DataProvider.ExecuteNonQuery("Update ThanhVien set TenKH=@TenKH,MatKhau=@MatKhau,Email=@Email,NgaySinh=@NgaySinh,DiaChi=@DiaChi,DienThoai=@DienThoai where TenDangNhap=@TenDangNhap", "@TenDangNhap",
                thanhVien.TenDangNhap, "@MatKhau", thanhVien.MatKhau
                , "@TenKH", thanhVien.TenKH, "@Email", thanhVien.Email,
                "@NgaySinh", thanhVien.NgaySinh, "@DiaChi", thanhVien.DiaChi,
                "@DienThoai", thanhVien.DienThoai);
            return true;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public bool Active(string userName, string activeCode)
    {

        
            string sql = "UPDATE ThanhVien SET TrangThai='True' WHERE TenDangNhap=@TenDangNhap AND ActiveCode=@ActiveCode ";
            DataProvider.ExecuteNonQuery(sql, "@TenDangNhap", userName, "@ActiveCode",activeCode);
            return true;
       
    }


    public bool KiemTraKHVip(string KhachHang)
    {
        try
        {
            string strSoDDH = DataProvider.ExecuteScalar("select count(*) from dondathang where khachhang = @KhachHang AND TrangThai>1 and TrangThai<5 ", "@KhachHang", KhachHang).ToString();
            int intSoDDH = Int32.Parse(strSoDDH);
            string strSQL = "select SoLanMua from ThamSo where NgayBD<=getDate() AND GETDATE()<=NgayKT ";
		                       
            string strSoLanMua = DataProvider.ExecuteScalar(strSQL).ToString();
            int intSoLanMua = Int32.Parse(strSoLanMua);
            if (intSoDDH > intSoLanMua)
                return true;
        }
        catch
        {
            
        }
        return false;
    }
    //Hanh
    public DataTable GetThanhVien1H()
    {
        try
        {
            return DataProvider.GetData("SELECT a.TenDangNhap,a.TenKH,a.Email,Count(b.mahd) as sohd,Sum((tongtien-giamgia)) as tongtien  FROM ThanhVien a,DonDatHang b where a.TrangThai=1 and a.TenDangNhap=b.KhachHang group by a.TenDangNhap,a.TenKH,a.Email Union" +
" SELECT distinct a.TenDangNhap,a.TenKH,a.Email,0 as sohd,0 as tongtien  FROM ThanhVien a,DonDatHang b where a.TrangThai=1 and a.TenDangNhap not in (select KhachHang from DonDatHang )");
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public void CapNhatMatKhau_ThayDoi(string matKhau, string tenDangNhap)
    {
        String sql = "UPDATE ThanhVien SET MatKhau=@p where TenDangNhap=@TenDangNhap";
        DataProvider.ExecuteNonQuery(sql,
            "@p", matKhau,
            "@TenDangNhap", tenDangNhap);
    }
    public string laysessionKichHoat(string email)
    {
        String sql = "select * from ThanhVien where email=@e";
        return (string)DataProvider.ExecuteScalar(sql, "@e", email);
    }
    public EThanhVien GetThanhVien_Email(string Email)
    {
        try
        {
            DataTable tableThanhVien = DataProvider.GetData("select * from ThanhVien where email=@e", "@e", Email);
            EThanhVien thanhVien = new EThanhVien();
            thanhVien.TenDangNhap = tableThanhVien.Rows[0]["TenDangNhap"].ToString();
            thanhVien.TenKH = tableThanhVien.Rows[0]["TenKH"].ToString();
            thanhVien.DiaChi = tableThanhVien.Rows[0]["DiaChi"].ToString();
            thanhVien.DienThoai = tableThanhVien.Rows[0]["DienThoai"].ToString();
            thanhVien.Email = tableThanhVien.Rows[0]["Email"].ToString();
            thanhVien.NgaySinh = DateTime.Parse(tableThanhVien.Rows[0]["NgaySinh"].ToString());
            return thanhVien;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public string layEmail(string email)
    {
        String sql = "Select Email from ThanhVien where Email=@Email";
        return (string)DataProvider.ExecuteScalar(sql, "@Email", email);

    }
    public string RandomString(int size)
    {
        StringBuilder sb = new StringBuilder();
        char ch;
        Random rand = new Random();
        for (int i = 0; i < size; i++)
        {
            ch = Convert.ToChar(Convert.ToInt32(rand.Next(97, 122)));
            sb.Append(ch);
        }
        return sb.ToString();
    }
    public void UpdateMatKhau(string matkhau, string email)
    {
        String sql = "UPDATE ThanhVien SET MatKhau=@p where Email=@e";
        DataProvider.ExecuteNonQuery(sql,
            "@p", matkhau,
            "@e", email);
    }
    public string layTenDangNhap(string tenDangNhap)
    {
        String sql = "Select TenDangNhap from ThanhVien where TenDangNhap=@TenDangNhap";
        return (string)DataProvider.ExecuteScalar(sql, "@TenDangNhap", tenDangNhap);

    }
    public string laySoCMND(string soCMND)
    {
        String sql = "Select CMND from ThanhVien where CMND=@CMND";
        return (string)DataProvider.ExecuteScalar(sql, "@CMND", soCMND);

    }
    public DataTable layHoaDonTheoKhachHang(string tenKH)
    {
        try
        {
            string sql = "select *,(tongtien-giamgia) thanhtien from DonDatHang where khachhang=@kh";
            return DataProvider.GetData(sql,"@kh",tenKH);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}